<?php

// Authenticate
require("class.AuthenticationManager.php");
require("class.CommandMenu.php");
if (!$authenticationManager->isLoggedIn() || !$authenticationManager->hasClearance(CLEARANCE_ADMINISTRATOR)) {
  Header("Location: login.php?redirect=$_SERVER[PHP_SELF]&clearanceRequired=Administrator");
  exit;
}



// Connect to database.
$dbh = dbConnect();
$contextUser = strtolower($_SESSION['contextUser']);

//define the command menu
include("timesheet_menu.inc");

// Set default months
setReportDateBis($year, $month, $day, $next_week, $prev_week, $next_month, $prev_month, $next_year, $prev_year, $time, $time_middle_month);

$startDay   = isset($_REQUEST['startDay'])   ? $_REQUEST['startDay']: 1;
$endDay     = isset($_REQUEST['endDay'])     ? $_REQUEST['endDay']: $day;
$startMonth = isset($_REQUEST['startMonth']) ? $_REQUEST['startMonth']: 1;
$endMonth   = isset($_REQUEST['endMonth'])   ? $_REQUEST['endMonth']: $month;
$startYear  = isset($_REQUEST['startYear'])  ? $_REQUEST['startYear']: $year;
$endYear    = isset($_REQUEST['endYear'])    ? $_REQUEST['endYear']: $year;


 $query = "select $TIMES_TABLE.proj_id, $TIMES_TABLE.task_id, $USER_TABLE.username, ".
                   "sec_to_time(unix_timestamp(end_time) - unix_timestamp(start_time)) as diff_time, ".
                   "(unix_timestamp(end_time) - unix_timestamp(start_time)) as diff, $PROJECT_TABLE.title, $TASK_TABLE.name, $TASK_TABLE.cir, $TASK_TABLE.source_project, $TASK_TABLE.target_project, ".
                   "date_format(start_time, '%Y/%m/%d') as start_date, trans_num, $TIMES_TABLE.uid, ".
                   "$USER_TABLE.first_name, $USER_TABLE.last_name, $USER_TABLE.company, $TIMES_TABLE.log_message " .
          "FROM     $USER_TABLE, $TIMES_TABLE, $PROJECT_TABLE, $TASK_TABLE ".
          "WHERE    $TIMES_TABLE.uid=$USER_TABLE.username ".
 	      "AND      end_time > 0 ".
 	      "AND      start_time >= '$startYear-$startMonth-$startDay' ".
		  "AND      start_time <= '$endYear-$endMonth-$endDay' ".
     	  "AND      $PROJECT_TABLE.proj_id = $TIMES_TABLE.proj_id ".
 	      "AND      $TASK_TABLE.task_id = $TIMES_TABLE.task_id ".
 	      "AND      end_time < '".date("Y-m-1",$next_month)."' ".
 	      "order by $USER_TABLE.uid, task_id, start_time";

	


$queryBis = "select   distinct  $TASK_TABLE.proj_id, $TASK_TABLE.task_id, $USER_TABLE.username, ".
                               "$TASK_TABLE.name, $TASK_TABLE.cir, $TASK_TABLE.source_project, $TASK_TABLE.target_project, ".
							   "$USER_TABLE.first_name, $USER_TABLE.last_name, $USER_TABLE.company ".
        		     "FROM      $USER_TABLE, $PROJECT_TABLE, $TASK_TABLE, $ASSIGNMENTS_TABLE, $TASK_ASSIGNMENTS_TABLE, $CLIENT_TABLE ".
                     "WHERE     $ASSIGNMENTS_TABLE.proj_id = $TASK_TABLE.proj_id ".
				     "AND       $TASK_ASSIGNMENTS_TABLE.task_id = $TASK_TABLE.task_id ".
					 "AND       $ASSIGNMENTS_TABLE.username = $USER_TABLE.username ".
					 "AND       $TASK_ASSIGNMENTS_TABLE.username = $USER_TABLE.username ".
					 "ORDER BY  $USER_TABLE.uid, $TASK_TABLE.name ";		  
		  
	  
		  
 

//run the query  
list($qh,$num)       = dbQuery($query);
list($qhBis,$numBis) = dbQuery($queryBis);
  
//define working varibales  
$total_time       = 0;
$grand_total_time = 0;







?>
<html>
    <head>
        <title>Timesheet.php Report: Annual state R&D </title>
        <?php include ("header.inc"); ?>
    </head>
    <body <?php include ("body.inc"); ?> >
        <?php include ("banner.inc"); ?>
        <form action="admin_report_annual_state_R_and_D.php" method="post">
            <input type="hidden" name="month" value="<?php echo $month; ?>">
            <input type="hidden" name="year"  value="<?php echo $year; ?>">
            <input type="hidden" name="day"   value="<?php echo $day; ?>">
            <input type="hidden" name="mode"  value="<?php echo $mode; ?>">
            <table width="100%"  border="0"   cellspacing="0" cellpadding="0">
              <tr>
                <td width="100%" class="face_padding_cell">
                    <table width="100%" border="0">
                        <tr>
							<td align="left" nowrap>
                                <table width="100%" height="100%" border="0" cellpadding="1" cellspacing="2">
                                    <tr>     
                                        <td align="left" width="10" class="outer_table_heading">
										    Start date :
										</td>
                                        <td align="left" width="40%">
											<?php 
											    dayButton("startDay", $startDay); 
											    monthButton("startMonth", $startMonth); 
												yearButton("startYear", $startYear);   
										    ?>
                                        </td>	

                                        <td align="left" width="5" class="outer_table_heading">
										    End date :
										</td>
                                        <td align="left" width="45%">
											<?php 
											    dayButton("endDay", $endDay); 
											    monthButton("endMonth", $endMonth); 
												yearButton ("endYear" , $endYear); 
											?>
											
                                        </td>
                                    </tr>
                                </table>
                            </td>                         
                        </tr>
                    </table>
                    <table width="100%" align="center" border="0" cellpadding="0" cellspacing="0" class="outer_table">
                        <tr>
                            <td>      
                                <table width="100%" border="0" cellpadding="0" cellspacing="0" class="table_body">
                                    <?php   
			                        if ($num == 0) 
			                        {
                                        print "  <tr>\n";
                                        print "    <td align=\"center\">\n";
                                        print "      <i><br>No days recorded.<br><br></i>\n";
                                        print "    </td>\n";
                                        print "  </tr>\n";
                                    }
                                    else 
			                        {
									    print "<tr>\n\t";
										// User name:
										print "<td valign=\"bottom\" align=\"left\" width=\"10%\" class=\"calendar_cell_disabled_right\">\n\t\t";
                                        print "&nbsp;&nbsp;Name<a>\n\t";
										print "</td>\n";
										// Company:
										print "<td valign=\"bottom\" align=\"left\" width=\"5%\" class=\"calendar_cell_disabled_right\">\n\t\t";
                                        print "&nbsp;&nbsp;Company<a>\n\t";
										print "</td>\n";
			                        	// Task name :
										print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_disabled_right\">\n\t\t";
                                        print "Task name\n\t&nbsp;";
										print "</td>\n\t";
                                        // CIR:
										print "<td valign=\"bottom\" align=\"left\" width=\"2%\" class=\"calendar_cell_disabled_right\">\n\t\t";
                                        print "&nbsp;&nbsp;CIR<a>\n\t";
										print "</td>\n";
										
										// Source project :
										print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_disabled_right\">\n\t\t";
                                        print "Source project</a>\n\t&nbsp;";
										print "</td>\n\t";
										// Target project :
										print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_disabled_right\">\n\t\t";
                                        print "Target project</a>\n\t&nbsp;";
										print "</td>\n\t";
                                    	// Duration
                                        print "<td valign=\"bottom\" align=\"right\" width=\"3%\" class=\"calendar_cell_disabled_right\">\n\t\t";
                                        print "&nbsp;&nbsp;Duration<a>\n\t";
										print "</td>\n";
										print "</tr>\n";
										print "<tr>\n\t";
									
                                        while ($data = dbResult($qh)) 
			                        	{
											// User name:
											print "<td valign=\"bottom\" align=\"left\" width=\"10%\" class=\"calendar_cell_right\">\n\t\t";
                                            print "&nbsp;&nbsp;".stripslashes("$data[first_name] $data[last_name]")."<a>\n\t";
											print "</td>\n";
											
											// Company:
											print "<td valign=\"bottom\" align=\"left\" width=\"5%\" class=\"calendar_cell_right\">\n\t\t";
                                            print "&nbsp;&nbsp;".stripslashes("$data[company]")."<a>\n\t";
											print "</td>\n";
											
			                        		// Task name :
											print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_right\">\n\t\t";
                                            print "<a href=\"javascript:void(0)\" onclick=\"javascript:window.open('task_info.php?task_id=$data[task_id]','Task Info','location=0,directories=no,status=no,scrollbar=yes,menubar=no,resizable=1,width=300,height=150')\">".stripslashes($data["name"])."</a>\n\t&nbsp;";
											print "</td>\n\t";
                                            // CIR:
											print "<td valign=\"bottom\" align=\"left\" width=\"2%\" class=\"calendar_cell_right\">\n\t\t";
                                            print "&nbsp;&nbsp;$data[cir]<a>\n\t";
											print "</td>\n";
											
											// Source project title :
											print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_right\">\n\t\t";
                                            print "<a href=\"javascript:void(0)\" onclick=\"javascript:window.open('proj_info.php?proj_id=$data[proj_id]','Task Info','location=0,directories=no,status=no,scrollbar=yes,menubar=no,resizable=1,width=300,height=150')\">".stripslashes($data["source_project"])."</a>\n\t&nbsp;";
											print "</td>\n\t";
											
											// Target project title :
											print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_right\">\n\t\t";
                                            print "<a href=\"javascript:void(0)\" onclick=\"javascript:window.open('proj_info.php?proj_id=$data[proj_id]','Task Info','location=0,directories=no,status=no,scrollbar=yes,menubar=no,resizable=1,width=300,height=150')\">".stripslashes($data["target_project"])."</a>\n\t&nbsp;";
											print "</td>\n\t";
                                    	    
											// Duration
                                    	    
											$duration = $data["diff"]/3600 / 8;
                                            print "<td valign=\"bottom\" align=\"right\" width=\"3%\" class=\"calendar_cell_right\">\n\t\t";
                                            print "&nbsp;&nbsp;$duration days<a>\n\t";
											print "</td>\n";
											
											print "</tr>\n";
                                            $total_time += $data["diff"];
                                            $grand_total_time += $data["diff"];
											
                                        }
                                        $formatted_time = $grand_total_time / 60 / 60 / 8;
                                    }
									// Without Imputation 
									if ($numBis != 0) 
			                        {
                                        while ($dataBis = dbResult($qhBis)) 
			                        	{
										    $isExist = 0;
										    list($qh,$num) = dbQuery($query);
											while ($data = dbResult($qh)) 
			                        	    {
	    										
											    if($data["first_name"] == $dataBis["first_name"] AND $data["last_name"] == $dataBis["last_name"] AND $data["company"] == $dataBis["company"] AND $data["name"] == $dataBis["name"] AND $data["source_project"] == $dataBis["source_project"] AND $data["target_project"] == $dataBis["target_project"])
												{
												    $isExist = 1;
												}
											}
											if ($isExist == 0)
											{
											    // User name:
											    print "<td valign=\"bottom\" align=\"left\" width=\"10%\" class=\"calendar_cell_right\">\n\t\t";
                                                print "&nbsp;&nbsp;".stripslashes("$dataBis[first_name] $dataBis[last_name]")."<a>\n\t";
											    print "</td>\n";
											    
											    // Company:
											    print "<td valign=\"bottom\" align=\"left\" width=\"5%\" class=\"calendar_cell_right\">\n\t\t";
                                                print "&nbsp;&nbsp;".stripslashes("$dataBis[company]")."<a>\n\t";
											    print "</td>\n";
											    
			                        		    // Task name :
											    print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_right\">\n\t\t";
                                                print "<a href=\"javascript:void(0)\" onclick=\"javascript:window.open('task_info.php?task_id=$dataBis[task_id]','Task Info','location=0,directories=no,status=no,scrollbar=yes,menubar=no,resizable=1,width=300,height=150')\">".stripslashes($dataBis["name"])."</a>\n\t&nbsp;";  
											    print "</td>\n\t";
                                                // CIR:
											    print "<td valign=\"bottom\" align=\"left\" width=\"2%\" class=\"calendar_cell_right\">\n\t\t";
                                                print "&nbsp;&nbsp;$dataBis[cir]<a>\n\t";
											    print "</td>\n";
											    
											    // Source project title :
											    print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_right\">\n\t\t";
                                                print "<a href=\"javascript:void(0)\" onclick=\"javascript:window.open('proj_info.php?proj_id=$dataBis[proj_id]','Task Info','location=0,directories=no,status=no,scrollbar=yes,menubar=no,resizable=1,width=300,height=150')\">".stripslashes($dataBis["source_project"])."</a>\n\t&nbsp;";
											    print "</td>\n\t";
											    
											    // Target project title :
											    print "<td valign=\"top\" align=\"left\" width=\"15%\" class=\"calendar_cell_right\">\n\t\t";
                                                print "<a href=\"javascript:void(0)\" onclick=\"javascript:window.open('proj_info.php?proj_id=$dataBis[proj_id]','Task Info','location=0,directories=no,status=no,scrollbar=yes,menubar=no,resizable=1,width=300,height=150')\">".stripslashes($dataBis["target_project"])."</a>\n\t&nbsp;";
											    print "</td>\n\t";
                                    	        
											    // Duration
                                                print "<td valign=\"bottom\" align=\"right\" width=\"3%\" class=\"calendar_cell_right\">\n\t\t";
                                                print "&nbsp;&nbsp;0 days<a>\n\t";
											    print "</td>\n";
											    print "</tr>\n";
											}
											
                                        }
                                    }
                                    ?>
                                        </tr>
                                    </td>
                                </table>
                            </td>
                        </tr>
                        <?php 
                            if ($num > 0)
                            {
                        ?>
                        <tr>
                            <td>
                                <table width="100%" border="0" cellspacing="0" cellpadding="0" class="table_bottom_panel">
                                    <tr>
                                        <td align="right" class="calendar_totals_line_monthly">            
                                        Yearly total:
                                            <span class="calendar_total_value_monthly"><?php echo $formatted_time ; echo " days"; ?></span>
                                        </td>
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <?php
                        }
                        ?>
                    </table>
                </td>
            </tr>
            </table>
         	<table width="100%" border="0" cellspacing="0" cellpadding="0">
        	    <tr>
        	    	<td align="center" nowrap>
        	    	   <a href="admin_report_annual_state_R_and_D_download_xls.php?month=<?php print $month; ?>&year=<?php print $year; ?>&query=<?php print $query; ?>&queryBis=<?php print $queryBis; ?>&startDay=<?php print $startDay; ?>&startMonth=<?php print $startMonth; ?>&startYear=<?php print $startYear; ?>&endDay=<?php print $endDay; ?>&endMonth=<?php print $endMonth; ?>&endYear=<?php print $endYear; ?>&mode=yearly">
        			       <input name="downloadButton" type="button" id="downloadButton" value="Download" onClick="DownloadAdminReportProject()"> </input> 
        			   </a>
        			   <br><br>
        	    	</td>
        	    </tr>
            </table>   
        </form>
        <?php include ("footer.inc");
        ?>
    </BODY>
</HTML>

